How To Solve the #SPILL Error in Excel [Easy Guide]

Even experienced Excel users occasionally encounter puzzling errors that can disrupt their workflow. One such error is the dreaded #SPILL error in Excel.

This error can be frustrating, especially when working with complex formulas and large datasets. Thankfully, resolving the #SPILL error is manageable. In this article, we’ll show you exactly how to fix it. With our step-by-step guide, discover how easy it is to fix SPILL Excel errors — in just a few simple clicks!

Table of Contents

The “#SPILL” error message in Excel indicates that a formula has produced more results than the target range can accommodate.

If the adjacent cells aren’t empty — or the formula spills over into already occupied cells — Excel displays the “#SPILL” error. This alerts you that the formula’s output cannot be properly displayed.

The “#SPILL” error was introduced with Excel’s dynamic array feature, which allows formulas to #SPILL their results into neighboring cells automatically.

It enables users to perform calculations on arrays of data without the need for complex array formulas or manually copying formulas across a range. While this feature enhances the efficiency and flexibility of calculations, it also introduces the possibility of encountering the “#SPILL” error.

With dynamic arrays, a single formula can generate multiple values — and automatically populate the adjacent cells — based on output size. This feature simplifies tasks such as filtering, sorting, and performing calculations on large data sets.

Dynamic arrays in Excel encompass several functions that leverage this capability, such as FILTER, SORT, UNIQUE, and SEQUENCE. These functions can take an array or a range of cells as input and return an array of results that automatically spills into the surrounding cells.

There are many reasons why a formula might return the #SPILL error in Excel. These different reasons all have different solutions.

The causes for the #SPILL error in Excel include the following:

Resolving the Excel #SPILL error either requires adjusting the target range or modifying the formula to ensure the output fits within the designated cells.

You’ll encounter the #SPILL error when the Excel formula needs to return results in cells that aren’t all empty.

There are two ways to solve the error if the range isn’t blank. When using the following two methods, values in the range won’t interfere with the results of the formula. This effectively removes the #SPILL error.

To clear the range, you can:

Once you’ve cleared the range, go to the cell with the formula, double-click on it, and press “Enter.”

Note: Sometimes, cells may look blank but have a formula or hidden value.

Unfortunately, dynamic arrays don’t work in table formats in Excel. Therefore, you may spot a #SPILL error if your data has been formatted to a table.

.

To solve this, convert your table into a range. Here’s how:

The #SPILL error results from a formula with a too-large range. Maximum #SPILL range size depends on the Excel version being used. In older versions of Microsoft Excel, this was never a problem: Before the dynamic arrays update, formulas would not automatically #SPILL over to other cells. However, newer versions often return a #SPILL error if the range is too extensive.

In Excel 365 and Excel 2019, the limit is approximately 17.2 billion cells. Earlier versions of Excel have more restrictive limits (but are still capable of dynamic arrays).

This typically happens when selecting an entire column as your range within the formula.

In this example, we want to add 1 to the values in column C. However, we’ve used the range C: C (which is why the formula returned a #SPILL error).

To solve this, we can use the actual value range instead of the whole column:

Note: The formula works better with a smaller defined range than the entire column.

Another way to solve this is to work with one cell first:

With this method, it doesn’t matter how big the range is: You can copy the formula to the rest of the cells and eliminate the #SPILL error.

One final way to solve the problem of the #SPILL range being too large is to apply “implicit intersection.” With the help of Excel’s implicit intersection function, you can use a formula to get a single value out of a group of cells. The value intersecting with the formula cell’s row and column is automatically chosen whenever there is an implicit intersection.

To apply it, add the @ symbol to your formula in front of the range as follows:

Merged cells are also an obstacle to dynamic arrays (which return the #SPILL error). As you can see,  a merged cell in the column for sorted data is an obstacle to the formula.

The easiest way to solve this problem is by unmerging the cells:

If you have trouble with merged cells, click on the yellow icon beside the error and choose “Select obstructing cell.” This will show you the cell that’s causing the #SPILL errors, indicating where to start troubleshooting.

Related: Excel vs. Google Sheets

A “range is unknown” issue may be caused when the formula cannot define the size or range of the output. This can happen for a variety of reasons. As a result, the #SPILL range becomes ambiguous.

This can be caused by a number of issues, such as:

Excel cannot establish the precise range of the output if the formula involves circular references. A circular reference occurs when the formula refers to its own cell — or depends on another cell that indirectly links back to it. Circular references may cause the calculation to fail and result in a #SPILL.

When a workbook is changed, certain Excel functions (referred to as volatile functions) recalculate. The values returned by these operations, like NOW(), RAND(), and TODAY(), fluctuate often. This can make identifying the #SPILL range tricky.

The output range may not be explicitly defined if the formula uses an array formula, which operates on numerous cells simultaneously. Since array formulas frequently provide several outcomes, Excel must choose the proper #SPILL range. However, if the array formula contains contradictions or certain complexities, the range can become ambiguous and result in the #SPILL error.

In our example, we used the following formula:

Using the formula above would return a #SPILL error.

Because the RANDBETWEEN function is volatile, the SEQUENCE function will return an unknown array.

When the #SPILL range is unknown, the only way to solve the error is by performing calculations with other non-volatile functions.

Related: Converting Excel to Google Sheets

There are two general methods to solve the #SPILL Excel error — and avoid cell obstacles:

To turn off #SPILL errors, put the symbol “@” before the formula that’s producing the error. The implicit intersection functionality will be enabled, automatically combining numerous values into one value.

Your VLOOKUP formula may return a #SPILL error if you have used an entire column as the data range. Try working with a more definite range in the formula to remove the error.

Your results from an array VLOOKUP function may also not have room to fit in the cell range. Try performing the search in a different part of the spreadsheet.

The #SPILL error in Excel SUMIF either occurs when there’s an obstacle in your #SPILL range or the range in the formula is too big. It also occurs in formulas like SORT, UNIQUE, TRANSPOSE, and VLOOKUP.

Try using the formula in a section of the spreadsheet that has more empty cells for the results to #SPILL into. You can also alter the range in your formula to only include the exact cells you’re using (i.e., C2:C15 instead of C: C).

A number of different factors can cause the #SPILL error in Excel. It can usually be fixed by removing existing content from cells (or merged cells) that are in the way.

Now that you know what a #SPILL error in Excel is — and all the possible causes — you should have everything you need to fix them.

If you found this article useful, be sure to check out our premium spreadsheet templates. When you use the promo code “SSP,” you’ll save 50%!

Get Premium Templates

Related: